package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.SortedSet;
import java.util.TreeSet;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;

import jxl.format.Border;

//import jxl.Range;
//import jxl.Workbook;
//import jxl.format.Alignment;
//import jxl.format.Border;
//import jxl.format.BorderLineStyle;
//import jxl.format.CellFormat;
//import jxl.format.Colour;
//import jxl.format.Format;
//import jxl.format.VerticalAlignment;
//import jxl.write.Label;
//import jxl.write.Number;
//import jxl.write.NumberFormats;
//import jxl.write.WritableCellFormat;
//import jxl.write.WritableFont;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBCSACRInputFormAppend1;
import org.compiere.model.MBCSACRInputFormAppend2Ext;
import org.compiere.model.MBCSACRInputFormAppend3_1;
import org.compiere.model.MBCSACRInputFormAppend3_1Ext;
import org.compiere.model.MBCSACRInputFormAppend3_2Ext;
import org.compiere.model.MBCSACRInputFormAppend4;
import org.compiere.model.MBCSACRInputFormCompany;
import org.compiere.model.MBCSACRInputFormPeriod;
import org.compiere.model.MBCSFRInputForm;
import org.compiere.model.MBCSFRInputFormColumn;
import org.compiere.model.MBCSFRInputFormCompany;
import org.compiere.model.MBCSFRInputFormLine;
import org.compiere.model.MBCSFRInputFormPeriod;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;
import org.utils.*;

public class BCS_UnderfinedOperationsReport extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private String Period = null;
	private BigDecimal Year = null;
	private int iYear = 0;
	private boolean isConsolidation = false;
	private BigDecimal C_BPartner1 = null;
	private String Application = "";
	private BigDecimal PeriodFrom = null;
	private MPeriod mperiod = null;
	private BigDecimal PeriodTo = null;
	private MPeriod mperiodTo = null;
	private BigDecimal Version = null;	
	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/** */
	private String path = "";

	private int AD_Org_ID = -1;
	private int AD_Client_ID = -1;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("PeriodFrom")) {
				PeriodFrom = (BigDecimal) para[i].getParameter();
			}else if (name.equals("PeriodTo")) {
				PeriodTo = (BigDecimal) para[i].getParameter();
			}else if (name.equals("C_Year_ID")) {
				Year = (BigDecimal) para[i].getParameter();
				iYear = Integer.parseInt(para[i].getInfo());
			} else if (name.equals("isConsolidation")) {
				isConsolidation = ((String) para[i].getParameter()).equals("Y");
			} else if (name.equals("C_BPartner_ID1")) {
				C_BPartner1 = (BigDecimal) para[i].getParameter();
		
			}else if (name.equals("BCS_Version")) {
				Version = (BigDecimal) para[i].getParameter();
			}
			
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		path = Util.localFilePath;
	}

	@Override
	protected String doIt() throws Exception {
		
		MBCSFRInputFormPeriod period = getMBCSFRInputFormPeriod(PeriodFrom, PeriodTo, Year, Version);
		List<MBCSFRInputFormCompany> companies = null;
//		List<MBCSFRInputFormCompany> company2 = null;
		
		if(period != null){
			companies = getMBCSFRInputFormCompany(period.get_ID(), null);
//			company2 = getMBCSFRInputFormCompany(period.get_ID(), C_BPartner2);
		}
		
		if(period == null || companies == null || companies.size() == 0)// || company2 == null || company2.size() == 0)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");		
		
		String file = "BCS_UnderfinedOperationsReport.xlsx";
		String filePath = Util.localFilePath + file;
//		String file = Util.localFilePath +"test.xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		//--------------------------------------------
		List<MBCSFRInputForm> forms = getMBCSFRInputForm(companies, "2,4,6");
		List<MBCSFRInputFormLine> lines = getMBCSFRInputFormLine(forms);
		List<MBCSFRInputFormColumn> columns = getMBCSFRInputFormColumn(lines);
		
//		List<MBCSFRInputForm> forms2 = getMBCSFRInputForm(company2, "2,4,6");
//		List<MBCSFRInputFormLine> lines2 = getMBCSFRInputFormLine(forms2);
//		List<MBCSFRInputFormColumn> columns2 = getMBCSFRInputFormColumn(lines2);
		
		
		List<MBCSFRInputFormColumn> columns2 = new ArrayList<MBCSFRInputFormColumn>();  
		
		for(int i = 0; i < columns.size(); i++){
			String s = columns.get(i).getValue1();
			if(s == null || s.equals("") || s.equals("0"))
				;
			else columns2.add(columns.get(i));
		}

//		for(int i = 0; i < columns2.size(); i++){
//			System.out.println(columns2.get(i).getValue1());
//		}

		columns = columns2;
		
		
		int countCompanies = companies.size();
		int countForms = forms.size();
		int countLines = lines.size();
		int countColumns = columns.size();
		
		Row R;
		Cell C;
		MPeriod p1 = new MPeriod(Env.getCtx(), PeriodFrom.intValue(), null);
		MPeriod p2 = new MPeriod(Env.getCtx(), PeriodTo.intValue(), null);
		
		R = sheet.getRow(2);
		C = R.getCell(0);
		if(C == null)
			C = R.createCell(0);
		
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(p1.getStartDate());
		String value  = "Период с: ";
		value += org.utils.DataUtils.getMonthNameRU(calendar.getTime()) + " ";
		value += calendar.get(calendar.YEAR);
		
		C.setCellValue(value);
		
		R = sheet.getRow(3);
		C = R.getCell(0);
		if(C == null)
			C = R.createCell(0);
		
		calendar.setTime(p2.getEndDate());
		value  = "Период по: ";
		value += org.utils.DataUtils.getMonthNameRU(calendar.getTime()) + " ";
		value += calendar.get(calendar.YEAR);
		
		C.setCellValue(value);

		List<line> list = new ArrayList<line>();
		List<line> list2 = new ArrayList<line>(); 
		
		int iRow = 6;
		boolean yes = false;
		for(int icompany = 0; icompany < countCompanies; icompany++){
			for(int iform = 0; iform < countForms; iform++){
				if(forms.get(iform).getBCS_FRInputForm_Company_ID() == companies.get(icompany).get_ID())
				for(int iline = 0; iline < countLines; iline++)
					if(forms.get(iform).get_ID() == lines.get(iline).getBCS_FRInputForm_ID())// && forms2.get(iform).get_ID() == lines2.get(iline).getBCS_FRInputForm_ID()){
						
						
						for(int icolumn = 0; icolumn < countColumns; icolumn++){
							if(lines.get(iline).get_ID() == columns.get(icolumn).getBCS_FRInputFormLine_ID())
								if(companies.get(icompany).getC_BPartner_ID()!=0 && columns.get(icolumn).getC_BPartner_ID()!=0)
									if(companies.get(icompany).getC_BPartner_ID()==C_BPartner1.intValue() || columns.get(icolumn).getC_BPartner_ID()==C_BPartner1.intValue()){
										int s = getIntAbs(columns.get(icolumn).getValue1());
										if(s!=0)
											list.add(new line(companies.get(icompany).getC_BPartner_ID(), columns.get(icolumn).getC_BPartner_ID(), s));
									}
						}
					}
						
				}
		
		
////		SortedSet s=new TreeSet();
		List<line> result = new ArrayList<line>();
		
//		for(int i=0; i<list.size(); i++){
//			System.out.println(list.get(i).bp1 + ","+ list.get(i).bp2 + ","+ list.get(i).value);
//		}
		
		
		for(int i=0; i<list.size(); i++){
			boolean b = true;
			for(int j=0; j<result.size(); j++){
				if(result.get(j).bp1 == list.get(i).bp1 && result.get(j).bp2 == list.get(i).bp2){
					result.get(j).value += list.get(i).value;
					b = false;
				}
			}
			if(b)
				result.add(list.get(i));

		}
//
//		List<line> result2 = new ArrayList<line>();
//		
//		for(int i=0; i<list2.size(); i++){
//			boolean b = true;
//			for(int j=0; j<result2.size(); j++){
//				if(result2.get(j).bp1 == list2.get(i).bp1 && result2.get(j).bp2 == list2.get(i).bp2){
//					result2.get(j).value += list2.get(i).value;
//					b = false;
//				}
//			}
//			if(b)
//				result2.add(list2.get(i));
//
//		}		
//		
		iRow = 6;
		for(int i=0; i< result.size(); i++){
			if(result.get(i).bp1 == C_BPartner1.intValue()){
				int value1 = 0;
				int value2 = 0;
				
				R = sheet.getRow(iRow);
					if(R == null)
						R = sheet.createRow(iRow);
				
					C = R.getCell(0);
					if(C == null)
						C = R.createCell(0);
					MBPartner bp = new MBPartner(Env.getCtx(), result.get(i).bp1, null);
					C.setCellValue(bp.getName());
	
					C = R.getCell(1);
					if(C == null)
						C = R.createCell(1);
					bp = new MBPartner(Env.getCtx(), result.get(i).bp2, null);
					C.setCellValue(bp.getName());
					
					C = R.getCell(2);
					if(C == null)
						C = R.createCell(2);
					C.setCellValue(result.get(i).value);
					value1 = result.get(i).value;
					
					Row r = sheet.getRow(6);
					for(int i2=0; i2 < 3; i2++){
						Cell c = r.getCell(i2);
						Cell c2 = R.getCell(i2);
						c2.setCellStyle(c.getCellStyle());
					}
	
					iRow++;
					
					boolean q = false;
					for(int j=0; j< result.size(); j++){
						
						if(!(result.get(i).bp1 == result.get(j).bp2 && result.get(i).bp2 == result.get(j).bp1))
							continue;
						q=true;
						R = sheet.getRow(iRow);
							if(R == null)
								R = sheet.createRow(iRow);
						
							C = R.getCell(0);
							if(C == null)
								C = R.createCell(0);
							MBPartner bp2 = new MBPartner(Env.getCtx(), result.get(j).bp1, null);
							C.setCellValue(bp2.getName());
	
							C = R.getCell(1);
							if(C == null)
								C = R.createCell(1);
							bp2 = new MBPartner(Env.getCtx(), result.get(j).bp2, null);
							C.setCellValue(bp2.getName());
							
							C = R.getCell(2);
							if(C == null)
								C = R.createCell(2);
							C.setCellValue(result.get(j).value);
							value2 = result.get(j).value;
							
							r = sheet.getRow(6);
							for(int i2=0; i2 < 3; i2++){
								Cell c = r.getCell(i2);
								Cell c2 = R.getCell(i2);
								c2.setCellStyle(c.getCellStyle());
							}
							
							iRow++;
						
					}
					if(!q){
						R = sheet.getRow(iRow);
						if(R == null)
							R = sheet.createRow(iRow);
					
						C = R.getCell(0);
						if(C == null)
							C = R.createCell(0);
						MBPartner bp2 = new MBPartner(Env.getCtx(), result.get(i).bp2, null);
						C.setCellValue(bp2.getName());

						C = R.getCell(1);
						if(C == null)
							C = R.createCell(1);
						bp2 = new MBPartner(Env.getCtx(), result.get(i).bp1, null);
						C.setCellValue(bp2.getName());
						
						C = R.getCell(2);
						if(C == null)
							C = R.createCell(2);
						C.setCellValue(0);
						value2 = 0;
						
						r = sheet.getRow(6);
						for(int i2=0; i2 < 3; i2++){
							Cell c = r.getCell(i2);
							Cell c2 = R.getCell(i2);
							c2.setCellStyle(c.getCellStyle());
						}
						
						iRow++;						
					}
				
					R = sheet.getRow(iRow);
					if(R == null)
						R = sheet.createRow(iRow);
				
					C = R.getCell(0);
					if(C == null)
						C = R.createCell(0);
					C.setCellValue("Разница");
	
					C = R.getCell(2);
					if(C == null)
						C = R.createCell(2);
					int diff = value1 - value2;
					if(diff < 0) diff *= -1;
					C.setCellValue(diff);
					
					r = sheet.getRow(8);
					for(int i2=0; i2 < 3; i2++){
						Cell c = r.getCell(i2);
						Cell c2 = R.getCell(i2);
						if(c2==null)
							c2 = R.createCell(i2);
						c2.setCellStyle(c.getCellStyle());
					}				
					
					
					iRow++;
			}				
		}
		
		
		//--------------------------------------------		
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");

	}
	
	public int getIntAbs(String v){
		int result = 0;
		
		try{
			result = Integer.parseInt(v);
		}catch(Exception e){}
		
		
		return result;
	}
	
	protected MBCSFRInputFormPeriod getMBCSFRInputFormPeriod(BigDecimal _periodFrom, BigDecimal _periodTo,  BigDecimal year,  BigDecimal Version) {
		MBCSFRInputFormPeriod period = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_FRInputForm_Period_ID FROM adempiere.BCS_FRInputForm_Period WHERE PeriodFrom = '"
				+ _periodFrom + "' AND PeriodTo = '"+ _periodTo +"'  AND C_Year_ID = '" + year + "' AND BCS_Version = '" + Version + "'";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				period = new MBCSFRInputFormPeriod(Env.getCtx(), rs.getInt(1),
						null);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return period;
	}

	
	protected List<MBCSFRInputFormCompany> getMBCSFRInputFormCompany(
			int MBCSFRInputFormPeriod_ID, BigDecimal c_bpartner) {
		List<MBCSFRInputFormCompany> company = new ArrayList<MBCSFRInputFormCompany>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_FRInputForm_Company_ID FROM adempiere.BCS_FRInputForm_Company \n WHERE BCS_FRInputForm_Period_ID = "
				+ MBCSFRInputFormPeriod_ID; 
		if(c_bpartner != null && c_bpartner.compareTo(new BigDecimal(0)) == 1)
				sql += " and C_BPartner_ID = " + c_bpartner;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				company.add(new MBCSFRInputFormCompany(Env.getCtx(),
						rs.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return company;
	}	
	
	
	private List<MBCSFRInputForm> getMBCSFRInputForm(List<MBCSFRInputFormCompany> companies, String codes) {
		
		List<MBCSFRInputForm> forms = new ArrayList<MBCSFRInputForm>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		for(MBCSFRInputFormCompany company: companies){
			sql = "SELECT f.BCS_FRInputForm_ID, f.BCS_FRSetupInputForm_ID, s.name, s.code FROM adempiere.BCS_FRInputForm f	\n"
					+ "	inner join adempiere.BCS_FRSetupInputForm s on s.BCS_FRSetupInputForm_ID = f.BCS_FRSetupInputForm_ID	\n"
					+ "	WHERE  BCS_FRInputForm_Company_ID = " + company.get_ID()
					+ "	and s.code in ("+codes+")	";

	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputForm form = new MBCSFRInputForm(Env.getCtx(), rs.getInt(1),	null);
					form.setName(rs.getString(3));
					form.setCode(rs.getInt(4));
					forms.add(form);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
//		

		return forms;
	}
	
	private List<MBCSFRInputFormLine> getMBCSFRInputFormLine(
			List<MBCSFRInputForm> forms) {
		
		List<MBCSFRInputFormLine> lines = new ArrayList<MBCSFRInputFormLine>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSFRInputForm form: forms){
		
			sql = "SELECT BCS_FRInputFormLine_ID FROM adempiere.BCS_FRInputFormLine WHERE  BCS_FRInputForm_ID = " + form.get_ID()
					+ " order by BCS_FRInputFormLine_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputFormLine line = new MBCSFRInputFormLine(Env.getCtx(), rs.getInt(1),	null);
					lines.add(line);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return lines;
	}

	private List<MBCSFRInputFormColumn> getMBCSFRInputFormColumn(
			List<MBCSFRInputFormLine> lines) {
		
		List<MBCSFRInputFormColumn> columns = new ArrayList<MBCSFRInputFormColumn>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSFRInputFormLine line: lines){
		
			sql = "SELECT BCS_FRInputFormColumn_ID FROM adempiere.BCS_FRInputFormColumn WHERE  BCS_FRInputFormLine_ID = " + line.get_ID()
					+ " order by seqno ";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputFormColumn column = new MBCSFRInputFormColumn(Env.getCtx(), rs.getInt(1),	null);
					columns.add(column);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return columns;
	}
	
	class line{
		line(int b1, int b2, int value){
			this.bp1 = b1;
			this.bp2 = b2;
			this.value = value;
		}
		int bp1 = 0;
		int bp2 = 0;
		int value = 0;
	}


}